
DROP FUNCTION IF EXISTS usp_[%tablename%]_ups([%paramtypes%]);

CREATE OR REPLACE FUNCTION usp_[%tablename%]_ups ([%params%]OUT InsertedID bigint)
RETURNS bigint AS
$BODY$
BEGIN
	
	-- Upsert (insert, if @id exists update) a record in [%tablename%] table.
	--  [%datestamp%] - auto generated

    LOOP
        -- first try to update the key
        UPDATE [%tablename%] SET [%update%]updated_dt=now() WHERE "id" = param_id ;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN 
		INSERT INTO [%tablename%] ([%insert%]created_dt,is_deleted) 
		values ([%values%]now(),0) returning id into InsertedID;
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
    
 
